package schedule.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DataRequester {

	private Connection connection;
	public DataRequester(){
		try{
		
			Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();

			// create connection to the database
			// use url found in JDBC connection properties
			// jdbc:derby://localhost:1527/sample;
			connection = DriverManager
					.getConnection("jdbc:derby://localhost:1527/sample");

			// create sql statement using the connection
			Statement statement = connection.createStatement();
		}catch(Exception ex){
			System.out.println("connection failed");
		}
		
	}
	//create sql statement to pull only studentTimes who have the given advisor
	public ArrayList<inputTime> getStudentTimes(int advisorID) {
		System.out.println("----getStudentTimes----");
		try {
			Statement selectStudentSql = connection.createStatement();
			//Insert mark's sql stmt here
			String sql = "select * from app.STUDENTTIME";
			ArrayList<inputTime> studentInputTimes = new ArrayList<inputTime>();
			ResultSet studentTimes = selectStudentSql.executeQuery(sql);
			while (studentTimes.next()){
				int startHr = studentTimes.getInt("stStartHour");
				int startMn = studentTimes.getInt("stStartMin");
				int endHr = studentTimes.getInt("stEndHour");
				int endMn = studentTimes.getInt("stEndMin");
				int day = studentTimes.getInt("stDay");
				int week = studentTimes.getInt("stWeek");
				String bannerID = studentTimes.getString("BannerId");				
				inputTime freshInput = new inputTime(startHr, startMn, endHr, endMn);
				freshInput.setCurrentDay(day);
				freshInput.setWeek(week);
				freshInput.setBannerID(bannerID);
				
				System.out.println("hr " + freshInput.getStartHr() + " day " + freshInput.getCurrentDay() + " bannerID " + freshInput.getBannerID());
				studentInputTimes.add(freshInput);
			}						
			return studentInputTimes;
		} catch (SQLException exception) {
			System.out.println(exception.getMessage());
			return null;
		}

	}
	
	public ArrayList<inputTime> getAdvisorTimes(int advisorID) {
		System.out.println("----getAdvisorTimes----");
		ArrayList<inputTime> advisorInputTimes = new ArrayList<inputTime>();
		try {
			Statement selectAdvisorSql = connection.createStatement();
			ResultSet select = selectAdvisorSql.executeQuery("select * from app.ADVISORTIME");
			while (select.next()) {
				int startHour = select.getInt("atstarthour");
				int startMin = select.getInt("atstartmin");
				int endHour = select.getInt("atendhour");
				int endMin = select.getInt("atendmin");
				int endDay = select.getInt("atday");
				int endWeek = select.getInt("atweek");
				int endAdvisorID = select.getInt("atAdvisorId");
				inputTime freshInput = new inputTime(startHour, startMin, endHour, endMin);
				freshInput.setCurrentDay(endDay);
				freshInput.setWeek(endWeek);
				freshInput.setAdvisorID(endAdvisorID);
				System.out.println("hr " + freshInput.getStartHr() + " day " + freshInput.getCurrentDay() + " advisorID " + freshInput.advisorID);
				advisorInputTimes.add(freshInput);				
			}					
			return advisorInputTimes;
		} catch (SQLException exception) {
			System.out.println(exception.getMessage());
			return null;
		}

	}
	public void insertIntoAdvisor(List<inputTime> allTimes){
		String sql = "";
		int i = 0;
		
		try{
			Statement insertTimes = connection.createStatement();
			while (i < allTimes.size()) {
				sql = "INSERT INTO app.AdvisorTime(atAdvisorId, atStartHour, atStartMin, atEndHour, atEndMin, atday) VALUES("
						+ 1
						+ ","
						+ allTimes.get(i).startHr
						+ ", "
						+ allTimes.get(i).startMn
						+ ", "
						+ allTimes.get(i).endHr
						+ ", "
						+ allTimes.get(i).endMn
						+ ", "
						//+ calculateDayNum(allTimes.get(i).currentDay)
						+ allTimes.get(i).currentDay
						+ ")";
//				/sql = String.format("INSERT INTO app.AdvisorTime(atAdvisorId, atStartHour, atStartMin, atEndHour, atEndMin, atday) VALUES('%s', '%s', '", args)
				int r = insertTimes.executeUpdate(sql);
				i++;
			}
		}catch(Exception ex){
			System.out.println(ex.getMessage());
		}
		
	}
}
